<?php
class Group_model extends CI_Model {

	function __construct()
    {
        parent::__construct();
    }
    /**
	 * 获取群信息
	 * @param int $id
	 */
	public function find_by_id($id) {
		$this->db->where('id', $id);
		return $this->db->get('groups')->row_array();
	}
  	/**
	 * 获取群信息
	 * @param int $id
	 */
	public function find_by_name($name) {
		$this->db->where('title', $name);
		return $this->db->get('groups')->row_array();
	}
	public function get_count_by_category_id ($category_id) {
		$sql = " select count(*) as total from groups g  where g.status=1 and  g.category_id=" . $category_id;
		$result = $this->db->query($sql)->row_array();
		return $result['total'];
	}
	public function find_by_category_id ($category_id, $offset = 0, $limit = 10) {
		$sql = " select g.*,u.nickname from groups g left join users u on g.created_by=u.id where g.status=1 and g.category_id=" . $category_id;
		$sql .= " limit " . $offset . ", " . $limit;
		return $this->db->query($sql)->result_array();
	}
	
	/**
	 * 删除合作组，同时删除组内成员
	 * @param unknown_type $group_id
	 */
	public function delete_group_by_id($group_id) {
		$sql = "delete from groups where id=" . intval($group_id);
		$this->db->query($sql);
		$sql = "delete from user_group where group_id = " . intval($group_id);
		$this->db->query($sql);
		return 1;
	}
	/**
	 * 查找某个组内的成员
	 * @param int $group_id
	 * @param string $role
	 * @param int $limit
	 */
	public function find_users_by_group_id($group_id, $role = '', $limit ='') {
		$sql = " select gu.user_id,u.nickname from user_group gu left join users u on gu.user_id=u.id where gu.status =1 and gu.group_id=" . $group_id;
		if(!empty($role)) {
			$sql .= " and gu.is_group_admin=1";
		}
		if(!empty($limit)) {
			$sql .= " order by gu.created desc limit " . $limit;
		}
		return $this->db->query($sql)->result_array();
	}
	/**
	 * 插入or更新group信息
	 * @param array $data
	 */
	public function add($data) {
		if (!empty($data['id'])) {
			$this->db->where('id', $data['id']);
			$this->db->update('groups', $data); 
			return $data['id'];
		} else {
			$this->db->insert("groups", $data);
			$new_expert_id = $this->db->insert_id();
			return $new_expert_id;
		}
		
	}
	/**
	 * 根据条件获取group的数目
	 * @param string $condition
	 */
	public function get_count_by_condition($condition) {
		$sql = " select count(*) as total from groups g  where " . $condition;
		$result = $this->db->query($sql)->row_array();
		return $result['total'];
	}
	/**
	 * 条件查询群组
	 * @param string $condition
	 * @param int $offset
	 * @param int $limit
	 */
	public function find_groups_by_condition($condition, $offset = null, $limit = null) {
		$sql = " select g.*,u.nickname,u.avatar from groups g left join users u on g.created_by=u.id where " . $condition;
		if (!empty($offset) && !empty($limit)) {
			$sql .= " limit " . $offset . ", " . $limit ;
		}
		return $this->db->query($sql)->result_array();
	}
	
	/**
	 * 根据创建者查询群组
	 * @param int $user_id
	 */
	public function find_groups_by_created_by($user_id) {
		$condition = " g.created_by=" . $user_id;
		$result = $this->find_groups_by_condition($condition);
		return $result;
	}
	
	public function get_count_by_created_by($user_id) {
		$condition = " g.created_by=" . $user_id;
		$result = $this->get_count_by_condition($condition);
		return $result;
	}
	/**
	 * 查询用户加入到群组
	 * @param unknown_type $user_id
	 */
	public function find_groups_user_join($user_id) {
		$sql = "select g.*,u.nickname from groups g 
				left join user_group ug on g.id= ug.group_id
				left join users u on g.created_by=u.id
		 		where g.status=1 and ug.status=1 and ug.user_id=" . $user_id .
				" group by g.id";
		
		return $this->db->query($sql)->result_array();
	}
	
	public function update_status($id, $status) {
		if ($status == -1) {
			$extra = ", closed_time=" . $this->db->escape(date("Y-m-d H:i:s"));
		} else {
			$extra = '';
		}
		$sql = "update groups set status=". $status . $extra . " where id=" . $id;
		return $this->db->query($sql);
	}
}